#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <time.h>
#include "database.h"
#include "log.h"

/**
 * @brief 连接到MySQL数据库
 * @return 返回数据库连接句柄，失败返回NULL
 */
MYSQL *dbConnect() {
    MYSQL *conn = mysql_init(NULL);
    if (!conn) {
        logError("Failed to initialize MySQL connection: %s", mysql_error(conn));
        return NULL;
    }
    
    // 连接数据库
    if (!mysql_real_connect(conn, DB_HOST, DB_USER, DB_PASSWORD, DB_NAME, 0, NULL, 0)) {
        logError("Failed to connect to database: %s", mysql_error(conn));
        mysql_close(conn);
        return NULL;
    }
    
    logInfo("Connected to MySQL database successfully");
    return conn;
}

/**
 * @brief 断开数据库连接
 * @param conn 数据库连接句柄
 */
void dbDisconnect(MYSQL *conn) {
    if (conn) {
        mysql_close(conn);
        logInfo("Disconnected from MySQL database");
    }
}

/**
 * @brief 向数据库中插入用户信息
 * @param conn 数据库连接句柄
 * @param user 用户信息结构体
 * @return 成功返回0，失败返回-1
 */
int dbInsertUser(MYSQL *conn, const UserInfo *user) {
    if (!conn || !user) {
        logError("Invalid database connection or user info");
        return -1;
    }
    
    // 准备SQL语句
    char query[256];
    sprintf(query, "INSERT INTO user_info (username, password, role) VALUES ('%s', '%s', '%s')", 
            user->username, user->password, user->role);
    
    // 执行SQL语句
    if (mysql_query(conn, query) != 0) {
        logError("Failed to insert user: %s", mysql_error(conn));
        return -1;
    }
    
    logInfo("User inserted successfully: %s", user->username);
    return 0;
}

/**
 * @brief 从数据库中获取用户信息
 * @param conn 数据库连接句柄
 * @param username 用户名
 * @param user 用户信息结构体（输出参数）
 * @return 成功返回0，失败返回-1
 */
int dbGetUser(MYSQL *conn, const char *username, UserInfo *user) {
    if (!conn || !username || !user) {
        logError("Invalid database connection or username or user info");
        return -1;
    }
    
    // 准备SQL语句
    char query[256];
    sprintf(query, "SELECT username, password, role FROM user_info WHERE username = '%s'", username);
    
    // 执行SQL查询
    if (mysql_query(conn, query) != 0) {
        logError("Failed to query user: %s", mysql_error(conn));
        return -1;
    }
    
    // 获取查询结果
    MYSQL_RES *result = mysql_store_result(conn);
    if (!result) {
        logError("Failed to store result: %s", mysql_error(conn));
        return -1;
    }
    
    // 检查结果行数
    if (mysql_num_rows(result) == 0) {
        logInfo("User not found: %s", username);
        mysql_free_result(result);
        return -1;
    }
    
    // 读取用户信息
    MYSQL_ROW row = mysql_fetch_row(result);
    if (row) {
        strncpy(user->username, row[0], sizeof(user->username) - 1);
        strncpy(user->password, row[1], sizeof(user->password) - 1);
        strncpy(user->role, row[2], sizeof(user->role) - 1);
    }
    
    mysql_free_result(result);
    logInfo("User retrieved successfully: %s", username);
    return 0;
}

/**
 * @brief 检查用户是否存在
 * @param conn 数据库连接句柄
 * @param username 用户名
 * @return 存在返回1，不存在返回0，出错返回-1
 */
int dbCheckUserExists(MYSQL *conn, const char *username) {
    if (!conn || !username) {
        logError("Invalid database connection or username");
        return -1;
    }
    
    // 准备SQL语句
    char query[256];
    sprintf(query, "SELECT COUNT(*) FROM user_info WHERE username = '%s'", username);
    
    // 执行SQL查询
    if (mysql_query(conn, query) != 0) {
        logError("Failed to check user existence: %s", mysql_error(conn));
        return -1;
    }
    
    // 获取查询结果
    MYSQL_RES *result = mysql_store_result(conn);
    if (!result) {
        logError("Failed to store result: %s", mysql_error(conn));
        return -1;
    }
    
    // 读取结果
    MYSQL_ROW row = mysql_fetch_row(result);
    int count = (row != NULL) ? atoi(row[0]) : 0;
    
    mysql_free_result(result);
    return count > 0 ? 1 : 0;
}

/**
 * @brief 向数据库中插入日志信息
 * @param conn 数据库连接句柄
 * @param id 日志ID
 * @param time 日志时间
 * @param logerror 错误信息
 * @param info 详细信息
 * @return 成功返回0，失败返回-1
 */
int dbInsertLog(MYSQL *conn, int id, const char *time, const char *logerror, const char *info) {
    if (!conn) {
        logError("Invalid database connection");
        return -1;
    }
    
    // 准备SQL语句
    char query[512];
    sprintf(query, "INSERT INTO logs (id, time, logerror, info) VALUES (%d, '%s', '%s', '%s')", 
            id, time ? time : "", logerror ? logerror : "", info ? info : "");
    
    // 执行SQL语句
    if (mysql_query(conn, query) != 0) {
        logError("Failed to insert log: %s", mysql_error(conn));
        return -1;
    }
    
    logInfo("Log inserted successfully, ID: %d", id);
    return 0;
}

// 插入生产数据
int dbInsertProductionData(MYSQL *conn, const char *waferid, const char *equipmentid, 
                          const char *defecttyme, const char *productionline, 
                          const char *reworkflag, const char *idaddress) {
    if (!conn) {
        logError("Invalid database connection");
        return -1;
    }
    
    char query[1024];
    snprintf(query, sizeof(query), 
             "INSERT INTO production_data (waferid, equipmentid, defecttyme, productionline, reworkflag, idaddress) "
             "VALUES ('%s', '%s', '%s', '%s', '%s', '%s')",
             waferid, equipmentid, defecttyme, productionline, reworkflag, idaddress);
    
    if (mysql_query(conn, query) != 0) {
        logError("Failed to insert production data: %s", mysql_error(conn));
        return -1;
    }
    
    logInfo("Production data inserted successfully for wafer: %s", waferid);
    return 0;
}

// 插入生产统计数据
int dbInsertProductionSummary(MYSQL *conn, const char *shift, const char *time, 
                             const char *lineno, int output, 
                             double qualifiedrata, double Agraderata, double reworkrata) {
    if (!conn) {
        logError("Invalid database connection");
        return -1;
    }
    
    char query[1024];
    snprintf(query, sizeof(query), 
             "INSERT INTO production_summary (shift, time, lineno, output, qualifiedrata, Agraderata, reworkrata) "
             "VALUES ('%s', '%s', '%s', %d, %f, %f, %f)",
             shift, time, lineno, output, qualifiedrata, Agraderata, reworkrata);
    
    if (mysql_query(conn, query) != 0) {
        logError("Failed to insert production summary: %s", mysql_error(conn));
        return -1;
    }
    
    logInfo("Production summary inserted successfully for line %s at %s", lineno, time);
    return 0;
}

// 按小时统计生产数据并保存到文件
int dbSummarizeHourlyData(MYSQL *conn, const char *timeRange) {
    if (!conn) {
        logError("Invalid database connection");
        return -1;
    }
    
    char query[1024];
    // 按产线统计数据
    snprintf(query, sizeof(query), 
             "SELECT "
             "CASE "
             "WHEN HOUR(NOW()) BETWEEN 8 AND 15 THEN '白班' "
             "ELSE '夜班' "
             "END as shift, "
             "'%s' as time, "
             "productionline as lineno, "
             "COUNT(*) as output, "
             "SUM(CASE WHEN defecttyme = '无缺陷' THEN 1 ELSE 0 END) / COUNT(*) as qualifiedrata, "
             "SUM(CASE WHEN defecttyme = '无缺陷' AND RAND() < 0.9 THEN 1 ELSE 0 END) / COUNT(*) as Agraderata, "
             "SUM(CASE WHEN reworkflag = '已返工' THEN 1 ELSE 0 END) / COUNT(*) as reworkrata "
             "FROM production_data "
             "WHERE DATE_FORMAT(NOW(), '%%Y-%%m-%%d %%H:00') = '%s' "
             "GROUP BY productionline",
             timeRange, timeRange);
    
    if (mysql_query(conn, query) != 0) {
        logError("Failed to summarize hourly data: %s", mysql_error(conn));
        return -1;
    }
    
    MYSQL_RES *result = mysql_store_result(conn);
    if (!result) {
        logError("Failed to store result: %s", mysql_error(conn));
        return -1;
    }
    
    // 保存统计结果到文件
    char filename[128];
    time_t now = time(NULL);
    struct tm *tm = localtime(&now);
    snprintf(filename, sizeof(filename), "production_summary_%d%02d%02d_%02d.txt",
             tm->tm_year + 1900, tm->tm_mon + 1, tm->tm_mday, tm->tm_hour);
    
    FILE *fp = fopen(filename, "w");
    if (!fp) {
        logError("Failed to open summary file: %s", filename);
        mysql_free_result(result);
        return -1;
    }
    
    fprintf(fp, "班次,时间,产线,产量,合格率,A品率,返工率\n");
    
    MYSQL_ROW row;
    while ((row = mysql_fetch_row(result))) {
        // 保存到文件
        fprintf(fp, "%s,%s,%s,%s,%s,%s,%s\n", 
                row[0], row[1], row[2], row[3], row[4], row[5], row[6]);
        
        // 保存到数据库
        dbInsertProductionSummary(conn, row[0], row[1], row[2], 
                                 atoi(row[3]), atof(row[4]), 
                                 atof(row[5]), atof(row[6]));
    }
    
    fclose(fp);
    mysql_free_result(result);
    
    logInfo("Hourly production data summarized and saved to %s", filename);
    return 0;
}